﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Drawing;
using System.Linq;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateHightLightSummary
    {
        public static void Write(int year, int month, int comYear, int comMonth)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //写入标题           
            Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 0, 0, 370, 15);
            titleBox.TextFrame.TextRange.Text = $"P&lHighlightsummary{year}{month.ToString("00")}";
            titleBox.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
            //写入表格
            int rowsCount = (year - 1) == comYear ? 18 : 24;
            Shape tableShape = currentslide.Shapes.AddTable(rowsCount, 9, 0, 25, currentslide.Master.Width, currentslide.Master.Height - 25);
            Table table = tableShape.Table;
            //预设整理格式
            foreach (Row row in table.Rows)
            {
                foreach (Cell cell in row.Cells)
                {
                    cell.Shape.TextFrame.TextRange.Font.Name = "微软雅黑";
                    cell.Shape.TextFrame.TextRange.Font.Size = 14;
                    cell.Shape.TextFrame.TextRange.ParagraphFormat.Alignment = PpParagraphAlignment.ppAlignCenter;
                    cell.Shape.TextFrame.VerticalAnchor = Microsoft.Office.Core.MsoVerticalAnchor.msoAnchorMiddle;
                }
            }
            // 编写标题行
            //写入MTD  要调转颜色RGB =>BGR
            int writeRow = 1;
            //1
            table.Cell(writeRow, 1).Shape.TextFrame.TextRange.Text = "类别";
            //1
            table.Cell(writeRow, 1).Shape.Fill.ForeColor.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            //1++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(255, 255, 255).ToArgb();
            //2++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "Sales";
            //3++ 或有
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"Sales VS.{(year - 1).ToString().Substring(2)}";
            }
            //4++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"Sales VS.{comYear.ToString().Substring(2)}";
            //5++ 或有
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD Sales VS.{(year - 1).ToString().Substring(2)}";
            }
            //6++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD Sales VS.{comYear.ToString().Substring(2)}";
            //7++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "GC";
            //8++ 或有
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"GC VS.{(year - 1).ToString().Substring(2)}";
            }
            //9++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"GC VS.{comYear.ToString().Substring(2)}";
            //10++ 或有
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD GC VS.{(year - 1).ToString().Substring(2)}";
            }
            //11++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD GC VS.{comYear.ToString().Substring(2)}";
            //12++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "AC";
            //13++ 或有
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"AC VS.{(year - 1).ToString().Substring(2)}";
            }
            //14++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"AC VS.{comYear.ToString().Substring(2)}";
            //15++
            if (rowsCount == 24)
            {
                table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD AC VS.{(year - 1).ToString().Substring(2)}";
            }
            //16++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = $"YTD AC VS.{comYear.ToString().Substring(2)}";
            //17++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "食品成本";
            //18++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "包装成本";
            //19++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "FOB";
            //20++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "SPMH";
            //21++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "薪资(3项)";
            //22++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "外部服务";
            //23++
            table.Cell(writeRow++, 1).Shape.TextFrame.TextRange.Text = "公共事业";
            //24
            table.Cell(writeRow, 1).Shape.TextFrame.TextRange.Text = "PAC";
            //开始写入数据            
            using (var db = new FinancialReportContext())
            {
                string[] restaurants = new string[] { "潭江店", "开大店", "白石店", "西粤店", "东湖店", "恩平店", "油十店", "汇总" };
                //先准备数据
                for (int i = 0; i < restaurants.Length; i++)
                {
                    writeRow = 1;
                    string flag = string.Empty;
                    string code = string.Empty;
                    Common.GetPLNameAndCode(restaurants[i], out flag, out code);
                    var cpldata = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Year == year && x.Month == month && x.Restaurant == flag);
                    var lypldata = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Year == year - 1 && x.Month == month && x.Restaurant == flag);
                    var compldata = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Year == comYear && x.Month == comMonth && x.Restaurant == flag);
                    var ytdcpldata = db.PLDatas.Where(x => x.IsPlan == false && x.Year == year && x.Month <= month && x.Restaurant == flag).AsNoTracking().ToList();
                    var ytdlypldata = db.PLDatas.Where(x => x.IsPlan == false && x.Year == year - 1 && x.Month <= month && x.Restaurant == flag).AsNoTracking().ToList();
                    var ytdcompldata = db.PLDatas.Where(x => x.IsPlan == false && x.Year == comYear && x.Month <= comMonth && x.Restaurant == flag).AsNoTracking().ToList();
                    //开始写入    1++               
                    table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = restaurants[i];
                    //MTD Sales   2++    
                    table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = cpldata.营业净收入.ToString("#,##0");
                    //MTD Sales VS.Last Year 3++或有
                    if (rowsCount == 24)
                    {
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(cpldata.营业净收入, lypldata.营业净收入), "p");
                    }
                    //Sales VS.Com Year  4++                  
                    Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(cpldata.营业净收入, compldata.营业净收入), "p");
                    //YTD Sales VS. Last Year  5++或有
                    if (rowsCount == 24)
                    {
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(ytdcpldata.Sum(x => x.营业净收入), ytdlypldata.Sum(x => x.营业净收入)), "p");
                    }
                    //YTD Sales VS. Com Year  6++                    
                    Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(ytdcpldata.Sum(x => x.营业净收入), ytdcompldata.Sum(x => x.营业净收入)), "p");
                    if (flag != "总表")
                    {
                        //准备非总表数据
                        var cplatdata = db.PlatformSales.FirstOrDefault(x => x.Uscode == code && x.Year == year && x.Month == month);
                        var complatdata = db.PlatformSales.FirstOrDefault(x => x.Uscode == code && x.Year == comYear && x.Month == comMonth);
                        var lyplatdata = db.PlatformSales.FirstOrDefault(x => x.Uscode == code && x.Year == (year - 1) && x.Month == month);
                        var ytdcplatdata = db.PlatformSales.Where(x => x.Uscode == code && x.Year == year && x.Month <= month).AsNoTracking().ToList();
                        var ytdlyplatdata = db.PlatformSales.Where(x => x.Uscode == code && x.Year == (year - 1) && x.Month <= month).AsNoTracking().ToList();
                        var ytdcomplatdata = db.PlatformSales.Where(x => x.Uscode == code && x.Year == comYear && x.Month <= comMonth).AsNoTracking().ToList();
                        var fob = db.Fobs.FirstOrDefault(x => x.Uscode == code && x.Year == year && x.Month == month);
                        var mgr = db.ManageNumbers.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == code);
                        var crew = db.CrewLabors.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == code);
                        var cmds = db.MdsQuantities.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == code);
                        //MTD GC 7++
                        table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = cplatdata.全店GC.ToString("N0");
                        //MTD GC VS. Last Year 8++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(cplatdata.全店GC, lyplatdata.全店GC), "p");
                        }
                        //GC VS. Com Year 9++
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(cplatdata.全店GC, complatdata.全店GC), "p");
                        //YTD GC VS. Last Year 10++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(ytdcplatdata.Sum(x => x.全店GC), ytdlyplatdata.Sum(x => x.全店GC)), "p");
                        }
                        //YTD GC VS. Com Year 11++
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(ytdcplatdata.Sum(x => x.全店GC), ytdcomplatdata.Sum(x => x.全店GC)), "p");
                        //MTD AC 12++
                        table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = cplatdata.全店AC.ToString("n");
                        //MTD Last Year AC 13++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, cplatdata.全店AC - lyplatdata.全店AC, "n");
                        }
                        //Com Year AC 14
                        Common.WriteVar(table, writeRow++, i + 2, cplatdata.全店AC - complatdata.全店AC, "n");
                        //YTD Last Year AC 15++或有
                        var ytdcac = ytdcplatdata.Sum(x => x.全店营业净收入) / ytdcplatdata.Sum(x => x.全店GC);
                        if (rowsCount == 24)
                        {                            
                            var ytdlyac = ytdlyplatdata.Sum(x => x.全店营业净收入) / ytdlyplatdata.Sum(x => x.全店GC);
                            Common.WriteVar(table, writeRow++, i + 2, ytdcac-ytdlyac, "n");
                        }
                        //YTD Com Year AC 16++
                        var ytdcomac= ytdcomplatdata.Sum(x => x.全店营业净收入) / ytdcomplatdata.Sum(x => x.全店GC);
                        Common.WriteVar(table, writeRow, i + 2, ytdcac-ytdcomac, "n");
                        //FOB   19=16+3
                        table.Cell((writeRow + 3), i + 2).Shape.TextFrame.TextRange.Text = fob.TotalControlPercentage?.ToString("p");
                        //SPMH  20=16+4
                        var spmh = cplatdata.全店营业净收入 / (Convert.ToDecimal(mgr.Total * 8 * 21.75) + crew.总计);
                        table.Cell((writeRow + 4), i + 2).Shape.TextFrame.TextRange.Text = spmh.ToString("N0");
                        //外部服务 22 = 16+6
                        table.Cell((writeRow + 6), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.外部服务 / cpldata.产品净收入).ToString("p") + "(" + cmds.Quantity.ToString() + ")";
                    }
                    else
                    {
                        //准备总表数据
                        var totalcplatdata = db.PlatformSales.Where(x => x.Year == year && x.Month == month).AsNoTracking().ToList();
                        var totalcomplatdata = db.PlatformSales.Where(x => x.Year == comYear && x.Month == comMonth).AsNoTracking().ToList();
                        var totallyplatdata = db.PlatformSales.Where(x => x.Year == (year - 1) && x.Month == month).AsNoTracking().ToList();
                        var totalytdcplatdata = db.PlatformSales.Where(x => x.Year == year && x.Month <= month).AsNoTracking().ToList();
                        var totalytdlyplatdata = db.PlatformSales.Where(x => x.Year == (year - 1) && x.Month <= month).AsNoTracking().ToList();
                        var totalytdcomplatdata = db.PlatformSales.Where(x => x.Year == comYear && x.Month <= comMonth).AsNoTracking().ToList();
                        var totalfob = db.Fobs.Where(x => x.Year == year && x.Month == month).AsNoTracking().ToList();
                        var totalmgr = db.ManageNumbers.Where(x => x.Year == year && x.Month == month).AsNoTracking().ToList();
                        var crew = db.CrewLabors.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == "总计");
                        var totalcac = totalcplatdata.Sum(x => x.全店营业净收入) / totalcplatdata.Sum(x => x.全店GC);
                        var totallyac = totallyplatdata.Sum(x => x.全店营业净收入) / totallyplatdata.Sum(x => x.全店GC);
                        var totalcomac = totalcomplatdata.Sum(x => x.全店营业净收入) / totalcomplatdata.Sum(x => x.全店GC);
                        var totalcmds = db.MdsQuantities.Where(x => x.Year == year && x.Month == month).AsNoTracking().ToList();
                        //MTD GC  7++
                        table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = totalcplatdata.Sum(x => x.全店GC).ToString("N0");
                        //MTD GC VS. Last Year 8++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(totalcplatdata.Sum(x => x.全店GC), totallyplatdata.Sum(x => x.全店GC)), "p");
                        }
                        //GC VS. Com Year 9++
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(totalcplatdata.Sum(x => x.全店GC), totalcomplatdata.Sum(x => x.全店GC)), "p");
                        //YTD GC VS. Last Year 10++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(totalytdcplatdata.Sum(x => x.全店GC), totalytdlyplatdata.Sum(x => x.全店GC)), "p");
                        }
                        //YTD GC VS. Com Year 11++
                        Common.WriteVar(table, writeRow++, i + 2, Calculate.CalVar(totalytdcplatdata.Sum(x => x.全店GC), totalytdcomplatdata.Sum(x => x.全店GC)), "p");
                        //MTD AC 12++
                        table.Cell(writeRow++, i + 2).Shape.TextFrame.TextRange.Text = totalcac.ToString("n");
                        //MTD Last Year AC 13++或有
                        if (rowsCount == 24)
                        {
                            Common.WriteVar(table, writeRow++, i + 2, totalcac - totallyac, "n");
                        }                        
                        //Com Year AC  14++
                        Common.WriteVar(table, writeRow++, i + 2, totalcac - totalcomac, "n");
                        //Last Year AC 15++
                        var totalytdcac = totalytdcplatdata.Sum(x => x.全店营业净收入) / totalytdcplatdata.Sum(x => x.全店GC);
                        if (rowsCount == 24)
                        {
                            var totalytdlyac = totalytdlyplatdata.Sum(x => x.全店营业净收入) / totalytdlyplatdata.Sum(x => x.全店GC);
                            Common.WriteVar(table, writeRow++, i + 2, totalytdcac - totalytdlyac, "n");
                        }
                        //Com Year AC  16
                        var totalytdcomac = totalytdcomplatdata.Sum(x => x.全店营业净收入) / totalytdcomplatdata.Sum(x => x.全店GC);
                        Common.WriteVar(table, writeRow, i + 2, totalytdcac - totalytdcomac, "n");
                        //FOB 19=16+3
                        table.Cell((writeRow + 3), i + 2).Shape.TextFrame.TextRange.Text = (totalfob.Sum(x => x.TotalControlCost) / totalfob.Sum(x => x.NetProductSales))?.ToString("p");
                        //SPMH  20=16+4
                        var totalspmh = totalcplatdata.Sum(x => x.全店营业净收入) / (Convert.ToDecimal(totalmgr.Sum(x => x.Total) * 8 * 21.75) + crew.总计);
                        table.Cell((writeRow + 4), i + 2).Shape.TextFrame.TextRange.Text = totalspmh.ToString("N0");
                        //外部服务  22=16+6
                        table.Cell((writeRow + 6), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.外部服务 / cpldata.产品净收入).ToString("p") + "(" + totalcmds.Sum(x => x.Quantity).ToString() + ")";
                    }
                    //食品成本 17=16+1
                    table.Cell((writeRow + 1), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.食品成本 / cpldata.产品净收入).ToString("p");
                    //包装成本 18=16+2
                    table.Cell((writeRow + 2), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.包装成本 / cpldata.产品净收入).ToString("p");
                    //薪资(3项) 21=16+5
                    table.Cell((writeRow + 5), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.薪资3项 / cpldata.产品净收入).ToString("p");
                    //公共事业  23=16+7
                    table.Cell((writeRow + 7), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.公共事业 / cpldata.产品净收入).ToString("p");
                    //PAC 24=16+8
                    table.Cell((writeRow + 8), i + 2).Shape.TextFrame.TextRange.Text = (cpldata.可控利润 / cpldata.产品净收入).ToString("p");
                }
            }
            tableShape.Height = currentslide.Master.Height - 25;
            tableShape.Width = currentslide.Master.Width;
        }
    }
}
